# # coding=utf-8
import openpyxl
import xlrd
import time
import requests
import json
import datetime
from auto_interface.testting import rpb_all_param
from auto_interface.testting import rpb_all_dealoracle

class Excel_rpb():
    @classmethod
    def excel(self, file_path=rpb_all_param.Param.new_path):
        data = xlrd.open_workbook(file_path)
        table = data.sheet_by_index(0)
        request_data = rpb_all_param.Param.rpb

        # 定义一个新的excel
        new_data = openpyxl.Workbook()
        new_sheet = new_data.active

        # 查看工作表
        data.sheet_names()
        # print("全部sheet页如下：\n" + str(data.sheet_names()))
        # print("总行数：" + str(table.nrows))
        start_time = time.strftime('%Y-%m-%d %H:%M:%S')
        starttime = datetime.datetime.now()
        print("开始时间：" + str(start_time))
        sum_false = 0

        for i in range(0, table.nrows):
            """
            print("卡口类型：%s" % rpbtype)
            print("通道编号：%s" % rpbid)
            print("车牌号：%s" % ve_licenseno)
            print("出入库类型：%s" % inexp_type)
            print("进出境关别：%s" % imep_code)
            print("进出方向：%s" % ioareaflag)
            print("运输方式：%s" % trspModecd)
            print("报关单布控解控：%s" % EntryNo_ISCTRL)
            print("毛重：%s" % wt_gross)
            print("集装箱：%s" % container_no)
            print("开关：%s" % kaiguan)
            print("重量(表名_类型_值)：%s" % other_wt)
            print("集装箱时间：%s" % other_container_time)
            print("车牌号时间：%s" % other_ve_time)
            print("备注：%s" % rmk)
            print("预期结果(提示语)：%s" % expect)
            print("是否通过：%s" % ispass)
            print("实际结果：%s" % actual)
            """
            # 表字段信息
            col = ("rpbtype","rbpid", "ve_licenseno","inexp_type", "imep_code","ioareaflag","trspModecd","EntryNo_ISCTRL", "wt_gross", "container_no","kaiguan","rmk","expect","ispass","actual")
            for param in rpb_all_param.Param.excel_rows_param.keys():
                value = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param[param]-1)
                request_data[param] = value
                Excel_rpb.excel_put(new_sheet=new_sheet, i=i, j=rpb_all_param.Param.excel_rows_param[param], param=value)

            if i != 0:
                request_data["gatetime"] = (time.strftime('%Y-%m-%d %H:%M:%S'))
                expect = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["expect"]-1)
                ioareaflag = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["ioareaflag"]-1)
                imep_code = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["imep_code"]-1)
                ve_licenseno = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["ve_licenseno"]-1)
                container_no = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["container_no"]-1)
                other_wt = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["other_wt"]-1)
                other_container_time = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["other_container_time"]-1)
                other_ve_time = Excel_rpb.excel_get(table=table, i=i, j=rpb_all_param.Param.excel_rows_param["other_ve_time"]-1)
                print("===========================")
                print("预计结果：" + expect)
                rpb_all_dealoracle.Deal_data.table_deal_with(ioareaflag, imep_code, ve_licenseno, container_no, other_wt, other_container_time, other_ve_time)
                response = Excel_rpb.post_requests(json.dumps(request_data), expect)
                ispass = response[0]
                if ispass is True:
                    sum_false += 1
                actual = response[1]
                Excel_rpb.excel_put(new_sheet, i, rpb_all_param.Param.excel_rows_param["ispass"], ispass)
                Excel_rpb.excel_put(new_sheet, i, rpb_all_param.Param.excel_rows_param["actual"], actual)

                # new_sheet.cell(i + 1, rpb_all_param.Param.excel_rows_param["ispass"], str(ispass)).number_format = '@'
                # new_sheet.cell(i + 1, rpb_all_param.Param.excel_rows_param["actual"], str(actual)).number_format = '@'

        endtime = datetime.datetime.now()
        use_time = (endtime-starttime).seconds
        print("共%s条用例，耗时：%s秒。\n通过%s条，失败%s条" % (table.nrows - 1, use_time, sum_false, table.nrows - sum_false - 1))
        new_data.save("../../全流程\全流程共%s条用例，通过%s条，失败%s条耗时%s秒===%s.xlsx" % (table.nrows - 1, sum_false, table.nrows - sum_false - 1, use_time, time.strftime('%m%d%H%M%S')))

    @classmethod
    def post_requests(self, data, expect):
        #测试环境
        url = 'http://192.168.125.224:9015/relist'

        try:
            headers = {"Content-Type": "application/json"}
            response = requests.post(url=url, headers=headers, data=data)
            print(data)
            ass_value = response.json()
            notes = ass_value.get("notes")

            status = False
            if expect in notes:
                status = True
            else:
                print(ass_value)
            return status, ass_value
        except Exception as E:
            return False, E, "请求异常"

    @classmethod
    def excel_get(self, table, i, j):
        value = table.cell(i, j).value
        return value

    @classmethod
    def excel_put(self, new_sheet, i, j, param):
        new_sheet.cell(i + 1, j+1, str(param)).number_format = '@'

if __name__ == "__main__":
    Excel_rpb.excel()